package com.taysear.dbservice;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
import com.taysear.dbservice.resources.XmlConverter;

import Ice.Current;

/**
 * 
 * @author Abdullahi
 */
@SuppressWarnings("serial")
public class DbServiceI extends _DbServicesDisp {
	String elementSeparator = "///";
	String itemValueSeparator = "====";
	
	BoneCP connectionPool = null;
	Connection connection = null;
	
	Boolean debug = true;

	public DbServiceI() { }
	
	/**
	 * Initializes the members
	 * 
	 * @param connectionPool
	 */
	public DbServiceI(BoneCP connectionPool) {
		this.connectionPool = connectionPool;
	}

	/**
	 * Start database connection pool.
	 * @return int
	 */
	public int startDatabaseConnectionPool() {
		System.out.println("::Starting Database Service::");
		// Read Database configuration properties

		String dbDriver = "com.mysql.jdbc.Driver";
		String dbUrl = "jdbc:mysql://localhost:3306/crown";
		String dbUsername = "root";
		String dbUserpwd = "";
		int dbPoolMin = 1;
		int dbPoolMax = 10;
		int dbPoolPartitions = 1;

		// Create Database Connection
		System.out.println("--- Loading Database Driver...");
		try {
			// load the database driver (make sure this is in your classpath!)
			Class.forName(dbDriver);
		} catch (Exception e) {
			System.err.println("Failed TO Load the db driver");
			return -1;
		}
		System.out.println("--- Connecting to Database...");
		try {
			// setup the connection pool
			BoneCPConfig config = new BoneCPConfig();
			config.setJdbcUrl(dbUrl); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb
			config.setUsername(dbUsername);
			config.setPassword(dbUserpwd);
			config.setMinConnectionsPerPartition(dbPoolMin);
			config.setMaxConnectionsPerPartition(dbPoolMax);
			config.setPartitionCount(dbPoolPartitions);
			connectionPool = new BoneCP(config); // setup the connection pool

		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
		return 0;
	}

	/**
	 * Gets active database connection
	 * 
	 * @return Connection
	 * @throws Exception
	 */
	public Connection getActiveDatabaseConnection() throws Exception {
		try {
			// get connection if it is null
			if (connection == null) {
				connection = connectionPool.getConnection();
			}
			
			boolean isValid = false;
			try {
				isValid = connection.isValid(0);
			} catch (Exception e) {
				isValid = false;
			}
			
			if (!isValid) {
				AtomicBoolean isDown = connectionPool.getDbIsDown();
				if (isDown.get())
					throw new SQLException("Database Connection is down");
				else
					connection = connectionPool.getConnection();

				// check validity again
				try {
					isValid = connection.isValid(0);
				} catch (Exception e) {
					throw new SQLException("Invalid Database Connection");
				}
				if (!isValid)
					throw new SQLException("Invalid Database Connection");
			}

			connection.setAutoCommit(true);
			return connection;
		} catch (SQLException e) {
			throw new Exception("[Failed to get connection from the pool.] #" + e.getErrorCode() + ", " + e);
		}
	}

	/**
	 * Sends Sms
	 * 
	 * @param mobileNo
	 * @param message
	 */
	private void sendSms(String mobileNo, String message) {
		System.out.println("Sending SMS to MobileNo: " + mobileNo);
		System.out.println("--- " + message);
	}

	/**
	 * Log message.
	 * 
	 * @param messageType
	 * @param message
	 */
	private void logMessage(MESSAGETYPE messageType, String message) {
		if (messageType == messageType.DEBUG) {
			System.out.println(message);
		} else if (messageType == messageType.ERR) {
			System.err.println(message);
		}
	}
	
	/**
	 * Gets the key and value pair in the map as one single string that can be printed.<br/>
	 * Example: Map values: [ (TEST1:'1') (TEST2:'2') (TEST3:'3') ]
	 * 
	 * @param values
	 * @return String 
	 */
	private String getMapValues(Map<String, String> values) {
		String result = "Map values: [";
		for (String k : values.keySet()) {
			result += " ("+ k + "=>'"+values.get(k) + "')";
		}
		result += " ]";
		return result;
	}
	
	/**
	 * Closes the connection, statement and resultset.
	 * 
	 * @param connection
	 * @param statement
	 * @param resultSet
	 * 
	 * @throws SQLException
	 */
	private void closeConnectionStatementResultSet(Connection connection, CallableStatement statement, ResultSet resultSet) throws SQLException {
		//connection.close();
		statement.close();
		//if(resultSet != null)
			//resultSet.close();
	}
	
	public ResultSet select(CallableStatement statement) throws Exception {
		Connection activeConnection = getActiveDatabaseConnection();
		
		ResultSet resultSet = statement.executeQuery();
		
		closeConnectionStatementResultSet(activeConnection, statement, resultSet);
		
		return resultSet;
	}

	@Override
	public ServiceResult login(ServiceParameter sp, Current __current) {
		String methodName = "login";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = "SELECT * FROM user where username = ? and password = ? limit 1";
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("username"));
				statement.setString(2, sp.param.get("password"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					String employeeId = resultSet.getString("employee");
					
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					
					HashMap<String, String> employee = getEmployeeById(activeConnection, Integer.parseInt(employeeId));
					sr.param.put("EMPLOYEE", employee.get("EMPLOYEE"));
					
					HashMap<String, String> country = getCountryByStationId(activeConnection, Integer.parseInt(employee.get("stationId")));
					sr.param.put("COUNTRY", country.get("COUNTRY"));
					
					String currency = getCurrencyByCountryId(activeConnection, Integer.parseInt(country.get("countryId")));
					sr.param.put("CURRENCY", currency);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
				}
			} catch (SQLException e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Get employee by its id.
	 * 
	 * @param activeConnection
	 * @param employeeId
	 * 
	 * @return HashMap<String, String>
	 * 
	 * @throws Exception
	 */
	private HashMap<String, String> getEmployeeById(Connection activeConnection, Integer employeeId) throws Exception {
		HashMap<String, String> result = new HashMap<String, String>();
		
		String sqlQuery = "select employeeId, firstname, lastname, e.station, e.substation " +
				"from employee e, station s, substation sub " +
				"where e.station = s.stationId and e.substation = sub.substationId " +
				"and employeeStatus = 1 and s.stationStatus = 1 and sub.stationStatus = 1 " +
				"and employeeId = ? limit 1";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		
		statement.setInt(1, employeeId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			result.put("stationId", resultSet.getString("station"));
			
			resultSet.beforeFirst();
			result.put("EMPLOYEE", XmlConverter.getXmlData(resultSet));
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured getEmployeeById.");
		}
		
		return result;
	}
	
	/**
	 * Get country by station id.
	 * 
	 * @param activeConnection
	 * @param stationId
	 * 
	 * @return HashMap<String, String>
	 * 
	 * @throws Exception
	 */
	private HashMap<String, String> getCountryByStationId(Connection activeConnection, Integer stationId) throws Exception {
		HashMap<String, String> result = new HashMap<String, String>();
		
		String sqlQuery = "select c.* from country c, station s where c.countryId = s.country and stationId = ? limit 1";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		
		statement.setInt(1, stationId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			result.put("countryId", resultSet.getString("countryId"));
			
			resultSet.beforeFirst();
			result.put("COUNTRY", XmlConverter.getXmlData(resultSet));
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured getCountryByStationId.");
		}
		
		return result;
	}
	
	/**
	 * Get currency by country id.
	 * 
	 * @param activeConnection
	 * @param countryId
	 * 
	 * @return String
	 * 
	 * @throws Exception
	 */
	private String getCurrencyByCountryId(Connection activeConnection, Integer countryId) throws Exception {
		String result = null;
		
		String sqlQuery = "select cu.* from currency cu, country co where cu.currencyId = co.currency and countryId = ?";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, countryId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			resultSet.beforeFirst();
			result = XmlConverter.getXmlData(resultSet);
			
			closeConnectionStatementResultSet(activeConnection, statement, resultSet);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
		}
		
		return result;
	}
	
	@Override
	public ServiceResult getPermissionsByUserIdAndPortalId(ServiceParameter sp,
			Current __current) {
		String methodName = "getPermissionsByUserIdAndPortalId";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = "select permissionId, name, description, parent, command from permission where portal = ?  order by parent";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				//statement.setString(1, sp.param.get("userId"));
				statement.setString(1, sp.param.get("portalId"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getLoadingsheetRoutesByDate(ServiceParameter sp, Current __current) {
		String methodName = "getLoadingsheetRoutesByDate";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				//String sqlQuery = "select loadingsheetId, routeId, amount, concat(getSubstationName(routeStart), ' - ', getSubstationName(routeEnd)) routeName from route r, loadingsheet l  where r.routeId = l.route and loadDate between '"+ sp.param.get("startDate") +"' and '"+ sp.param.get("endDate") +"'";
				//String sqlQuery = "select loadingsheetId, routeId, amount, concat(getSubstationName(routeStart), ' - ', getSubstationName(routeEnd)) routeName from route r, loadingsheet l  where r.routeId = l.route and date between ? and ?";
				String sqlQuery = "select loadingsheetId, routeTime routeId, amount, concat(departureTime, ' ', ampm, ' ',getSubstationName(routeStart), ' - ', getSubstationName(routeEnd)) routeName, reportingTime, departureTime, ampm from route r, loadingsheet l, route_time rt, time t  where r.routeId = rt.route and rt.routeTimeId = l.routeTime and rt.time = t.timeId and date between ? and ? order by route, timeId ";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("startDate"));
				statement.setString(2, sp.param.get("endDate"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Get for the selected route the available times.
	 * 
	 * @param activeConnection
	 * @param routeTimeId
	 * 
	 * @return XML data containing records of route time table.
	 * 
	 * @throws Exception
	 */
	private String getRouteTimes(Connection activeConnection, Integer routeTimeId) throws Exception {
		String result = null;
		
		String sqlQuery = "select routeTimeId, reportingTime, departureTime, ampm from route_time rt, time t where t.timeId = rt.time and routeTimeId = ?";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, routeTimeId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			resultSet.beforeFirst();
			result = XmlConverter.getXmlData(resultSet);
			
			closeConnectionStatementResultSet(activeConnection, statement, resultSet);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
		}
		
		return result;
	}

	@Override
	public ServiceResult getAvailableSeatsForLoadingsheetByRouteTimeId(
			ServiceParameter sp, Current __current) {
		String methodName = "getAvailableSeatsForLoadingsheetByRouteTimeId";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				//String sqlQuery = "select loadingsheetTimeSeatId seatId, description from loadingsheet l, bus b, loadingsheet_time lt, time t, loadingsheet_time_seat lts, seat s where b.busId = l.bus and l.loadingsheetId = lt.loadingsheet and t.timeId = lt.time and lt.loadingsheetTimeId = lts.loadingsheetTime and s.seatId = lts.seat and isAvailable = 1 and route = ? and timeId = ?";
				String sqlQuery = "select routeTimeSeatId seatId, description from loadingsheet l, bus b, route_time rt, time t, route_time_seat rts, seat s where b.busId = l.bus and l.routeTime = rt.routeTimeId and t.timeId = rt.time and rt.routeTimeId = rts.routeTime and s.seatId = rts.seat and isAvailable = 1 and routeTimeId = ?";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("routeTimeId"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getAllNationalities(ServiceParameter sp,
			Current __current) {
		String methodName = "getAllNationalities";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = "select * from country";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult saveBooking(ServiceParameter sp, Current __current) {
		String methodName = "saveBooking";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				activeConnection.setAutoCommit(false);
				
				Integer bookingId = saveBookingToDb(activeConnection, sp.param);

				if (bookingId != null) {
					sr.param.put("bookingId", ""+bookingId);
					
					Boolean seatStatusChanged = changeSeatStatus(activeConnection, Integer.parseInt(sp.param.get("seatId")), Integer.parseInt(sp.param.get("seatStatus")));
					
					Boolean bookingHistory = createBookingHistory(activeConnection, Integer.parseInt(sp.param.get("bookingAction")), bookingId, Integer.parseInt(sp.param.get("userId")), "booked and paid.");
					
					String actionDate = getActionDateBooking(activeConnection, bookingId, Integer.parseInt(sp.param.get("bookingAction")));
					sr.param.put("actionDate", actionDate);
					
					if(seatStatusChanged && bookingHistory)
						sr.status = true;
					
					activeConnection.commit();
					activeConnection.setAutoCommit(true);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					throw new SQLException("An error occured "+ methodName);
				}
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Saves booking to DB.
	 * 
	 * @param activeConnection
	 * @param param
	 * 
	 * @return Integer
	 * 
	 * @throws Exception
	 */
	private Integer saveBookingToDb(Connection activeConnection, Map<String, String> param) throws Exception {
		Integer id = null;
		
		String sqlQuery = "insert into booking (loadingsheet, travelDate, reportingDate, routeTimeSeat, status, customerName, customerMobile, nationality, idNumber, childAge, fromSubstation, toSubstation, amount, currencyName, pickupPoint, isOneWay) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		
		statement.setInt(1, Integer.parseInt(param.get("loadingsheetId")));
		statement.setString(2, param.get("travelDate"));
		statement.setString(3, param.get("reportingDate"));
		statement.setInt(4, Integer.parseInt(param.get("seatId")));
		statement.setInt(5, Integer.parseInt(param.get("bookingStatus")));
		statement.setString(6, param.get("name"));
		statement.setString(7, param.get("mobile"));
		statement.setInt(8, Integer.parseInt(param.get("nationality")));
		statement.setString(9, param.get("idNumber"));
		statement.setInt(10, (param.get("childAge") == null | "".equalsIgnoreCase(param.get("childAge"))) ? 0: Integer.parseInt(param.get("childAge")));
		statement.setInt(11, Integer.parseInt(param.get("fromSubstation")));
		statement.setInt(12, Integer.parseInt(param.get("toSubstation")));
		statement.setDouble(13, Double.parseDouble(param.get("amount")));
		statement.setString(14, param.get("currencyName"));
		statement.setString(15, param.get("pickupPoint"));
		statement.setInt(16, Integer.parseInt(param.get("isOneWay")));
		
		int result = statement.executeUpdate();
		
		if (result == 1) {
			ResultSet resultSet = statement.getGeneratedKeys();
			resultSet.next();
			id = resultSet.getInt(1);
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured saveBookingToDb.");
		}
		
		return id;
	}
	
	/**
	 * Changes the satus of a seat.
	 * 
	 * @param activeConnection
	 * @param routeTimeSeatId
	 * @param seatStatus
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean changeSeatStatus(Connection activeConnection, Integer routeTimeSeatId, Integer seatStatus) throws Exception {
		Boolean resultBoolean = false;
		if(checkSeatAvailability(activeConnection, routeTimeSeatId, seatStatus)) {
			String sqlQuery = "update route_time_seat set isAvailable = ? where routeTimeSeatId = ?";
			
			CallableStatement statement = activeConnection.prepareCall(sqlQuery);
			
			statement.setInt(1, seatStatus);
			statement.setInt(2, routeTimeSeatId);
			
			int result = statement.executeUpdate();
			if (result == 1) {
				resultBoolean = true;
				
				closeConnectionStatementResultSet(activeConnection, statement, null);
			} else {
				closeConnectionStatementResultSet(activeConnection, statement, null);
				throw new SQLException("An error occured changeSeatStatus.");
			}
		}
		
		return resultBoolean;
	}
	
	/**
	 * Changes the satus of a seat.
	 * 
	 * @param activeConnection
	 * @param routeTimeSeatId
	 * @param seatStatus
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean checkSeatAvailability(Connection activeConnection, Integer routeTimeSeatId, Integer seatStatus) throws Exception {
		Boolean resultBoolean = false;
		
		String sqlQuery = "select routeTimeSeatId, description from route_time_seat rts, seat s where s.seatId = rts.seat and isAvailable = ? and routeTimeSeatId = ?";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		
		statement.setInt(1, seatStatus);
		statement.setInt(2, routeTimeSeatId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			String description = resultSet.getString("description");
			closeConnectionStatementResultSet(activeConnection, statement, resultSet);
			throw new TException(0, "Seat "+ description +" is already booked.", "");
		} else {
			resultBoolean = true;
			closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
		}
		
		return resultBoolean;
	}
	
	/**
	 * Gets the date that a action is occurred on a booking.
	 * 
	 * @param activeConnection
	 * @param bookingId
	 * @param actionId
	 * 
	 * @return String
	 * 
	 * @throws Exception
	 */
	private String getActionDateBooking(Connection activeConnection, Integer bookingId, Integer actionId) throws Exception {
		String result = null;
		
		String sqlQuery = "select date from booking_history where booking = ? and bookingAction = ?";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, bookingId);
		statement.setInt(2, actionId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			result = resultSet.getString("date");
			
			closeConnectionStatementResultSet(activeConnection, statement, resultSet);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
		}
		
		return result;
	}
	
	/**
	 * Create a booking history.
	 * 
	 * @param activeConnection
	 * @param bookingActionId
	 * @param bookingId
	 * @param userId
	 * @param description
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean createBookingHistory(Connection activeConnection, Integer bookingActionId, Integer bookingId, Integer userId, String description) throws Exception{
		Boolean resultBoolean = false;
		String sqlQuery = "insert into booking_history (bookingAction, booking, user, description, date) values(?, ?, ?, ?, now())";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, bookingActionId);
		statement.setInt(2, bookingId); 
		statement.setInt(3, userId);
		statement.setString(4, description);
		
		int result = statement.executeUpdate();
		
		if (result == 1) {
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured at createBookingHistory.");
		}
		return resultBoolean;
	}

	@Override
	public ServiceResult getSubstationsByRouteTimeId(ServiceParameter sp,
			Current __current) {
		String methodName = "getSubstationsByRouteTimeId";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				//String sqlQuery = "select s.* from substation s, route_detail rd where s.substationId = rd.substation and stationStatus = 1 and route = ? order by routeDetailId";
				String sqlQuery = "select s.* from substation s, route_detail rd, route_time rt where s.substationId = rd.substation and stationStatus = 1 and rd.route = rt.route and routeTimeId = ? order by routeDetailId";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("routeTimeId"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getBusFareByFromToSubstationId(ServiceParameter sp,
			Current __current) {
		String methodName = "getBusFareByFromToSubstationId";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = "select busFareId, amount, name currencyName from bus_fare bf, currency c where bf.currency = c.currencyId and fromSubstation = ? and toSubstation = ?";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("fromSubstation"));
				statement.setString(2, sp.param.get("toSubstation"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult searchBooking(ServiceParameter sp, Current __current) {
		String methodName = "searchBooking";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String filterBy = "1".equals(sp.param.get("filterBy")) ? "bookingId" : "customerMobile";
				String sqlQuery = "select *,getBookingStatusDescription(status) statusDescription, getSeatDescription(routeTimeSeat) seatDescription, getSubstationName(fromSubstation) fromSubstationDescription, getSubstationName(toSubstation) toSubstationDescription from booking where "+ filterBy +" = ?";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("filterValue"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult setBookingOpen(ServiceParameter sp, Current __current) {
		String methodName = "setBookingOpen";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				
				activeConnection.setAutoCommit(false);
				
				Boolean bookingStatusChanged = changeBoookingStatus(activeConnection, Integer.parseInt(sp.param.get("bookingId")), Integer.parseInt(sp.param.get("bookingStatus")));
				
				Boolean seatStatusChanged = changeSeatStatus(activeConnection, Integer.parseInt(sp.param.get("routeTimeSeatId")), Integer.parseInt(sp.param.get("seatStatus")));
				
				Boolean bookingHistory = createBookingHistory(activeConnection, Integer.parseInt(sp.param.get("bookingAction")), Integer.parseInt(sp.param.get("bookingId")), Integer.parseInt(sp.param.get("userId")), sp.param.get("description"));
				
				Integer voucherId = createVoucher(activeConnection, Integer.parseInt(sp.param.get("bookingId")), new BigDecimal(sp.param.get("amount")), Integer.parseInt(sp.param.get("currencyId")));
				
				if(bookingStatusChanged && seatStatusChanged && bookingHistory && voucherId != null) {
					sr.param.put("voucherId", voucherId.toString());
					sr.status = true;
				}
				activeConnection.commit();
				activeConnection.setAutoCommit(true);
				
				if(debug)
					System.out.println(methodName+" returned result, " + getMapValues(sr.param));
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Change the status of a booking.
	 * 
	 * @param activeConnection
	 * @param bookingId
	 * @param bookingStatus
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean changeBoookingStatus(Connection activeConnection, Integer bookingId, Integer bookingStatus) throws Exception {
		Boolean resultBoolean = false;
		
		String sqlQuery = "update booking set status = ?, routeTimeSeat = null, loadingsheet = null where bookingId = ?";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		
		statement.setInt(1, bookingStatus);
		statement.setInt(2, bookingId);
		
		int result = statement.executeUpdate();
		if (result == 1) {
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured changeBoookingStatus.");
		}
		
		return resultBoolean;
	}
	
	/**
	 * Create voucher for an open booking.
	 * 
	 * @param activeConnection
	 * @param bookingId
	 * @param amount
	 * @param currencyId
	 * 
	 * @return Integer
	 * 
	 * @throws Exception
	 */
	private Integer createVoucher(Connection activeConnection, Integer bookingId, BigDecimal amount, Integer currencyId) throws Exception {
		Integer voucherId = null;
		
		String sqlQuery = "insert into voucher (amount, balance, currency, isUsed) values (?, ?, ?, 0)";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		
		statement.setBigDecimal(1, amount);
		statement.setBigDecimal(2, amount);
		statement.setInt(3, currencyId);
		
		int result = statement.executeUpdate();
		if (result == 1) {
			ResultSet resultSet = statement.getGeneratedKeys();
			resultSet.next();
			
			voucherId = resultSet.getInt(1);
			createVoucherBooking(activeConnection, bookingId, voucherId);
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured createVoucher.");
		}
		
		return voucherId;
	}
	
	/**
	 * Create voucher booking entry.
	 * 
	 * @param activeConnection
	 * @param bookingId
	 * @param voucherId
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean createVoucherBooking(Connection activeConnection, Integer bookingId, Integer voucherId) throws Exception {
		Boolean resultBoolean = false;
		
		String sqlQuery = "insert into voucher_booking (voucher, booking, date) values (?, ?, now())";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		
		statement.setInt(1, voucherId);
		statement.setInt(2, bookingId);
		
		int result = statement.executeUpdate();
		if (result == 1) {
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured createVoucherBooking.");
		}
		
		return resultBoolean;
	}

	@Override
	public ServiceResult cancelBooking(ServiceParameter sp, Current __current) {
		String methodName = "cancelBooking";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				
				activeConnection.setAutoCommit(false);
				
				Boolean bookingStatusChanged = changeBoookingStatus(activeConnection, Integer.parseInt(sp.param.get("bookingId")), Integer.parseInt(sp.param.get("bookingStatus")));
				
				Boolean seatStatusChanged = changeSeatStatus(activeConnection, Integer.parseInt(sp.param.get("routeTimeSeatId")), Integer.parseInt(sp.param.get("seatStatus")));
				
				Boolean bookingHistory = createBookingHistory(activeConnection, Integer.parseInt(sp.param.get("bookingAction")), Integer.parseInt(sp.param.get("bookingId")), Integer.parseInt(sp.param.get("userId")), sp.param.get("description"));
				
				if(bookingStatusChanged && seatStatusChanged && bookingHistory)
					sr.status = true;
				
				activeConnection.commit();
				activeConnection.setAutoCommit(true);
				
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult changeBooking(ServiceParameter sp, Current __current) {
		String methodName = "changeBooking";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				activeConnection.setAutoCommit(false);
				
				Boolean bookingStatusChanged = changeBoookingStatus(activeConnection, Integer.parseInt(sp.param.get("bookingId")), Integer.parseInt(sp.param.get("bookingStatus")));
				
				Integer bookingId = saveBookingToDb(activeConnection, sp.param);
				Boolean bookingHistory = createBookingHistory(activeConnection, Integer.parseInt(sp.param.get("bookingAction")), bookingId, Integer.parseInt(sp.param.get("userId")), "booked and paid.");

				if (bookingStatusChanged && bookingHistory && bookingId != null) {
					sr.param.put("bookingId", ""+bookingId);
					
					if(!"".equalsIgnoreCase(sp.param.get("oldSeatId")))
						changeSeatStatus(activeConnection, Integer.parseInt(sp.param.get("oldSeatId")), Integer.parseInt(sp.param.get("oldSeatStatus")));
					
					Boolean seatStatusChanged = changeSeatStatus(activeConnection, Integer.parseInt(sp.param.get("seatId")), Integer.parseInt(sp.param.get("seatStatus")));
					
					Boolean oldBookingHistory = createBookingHistory(activeConnection, Integer.parseInt(sp.param.get("bookingAction")), Integer.parseInt(sp.param.get("bookingId")), Integer.parseInt(sp.param.get("userId")), "changed, new booking is "+bookingId +".");
					
					String actionDate = getActionDateBooking(activeConnection, bookingId, Integer.parseInt(sp.param.get("bookingAction")));
					sr.param.put("actionDate", actionDate);
					
					if(seatStatusChanged && oldBookingHistory)
						sr.status = true;
					
					activeConnection.commit();
					activeConnection.setAutoCommit(true);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					throw new SQLException("An error occured "+ methodName);
				}
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getSystemConfigurations(ServiceParameter sp,
			Current __current) {
		String methodName = "getSystemConfigurations";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = "select * from system_configuration";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getAllSubstations(ServiceParameter sp,
			Current __current) {
		String methodName = "getAllSubstations";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = "select * from substation s";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getRoutesBySubstationsAndDates(ServiceParameter sp,
			Current __current) {
		String methodName = "getRoutesBySubstationsAndDates";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String operand = new Boolean(sp.param.get("isDeparture")) ? "<" : ">";
				String sqlQuery = " select distinct loadingsheetId, routeTime routeId, amount, concat(departureTime, ' ', ampm, ' ',getSubstationName(routeStart), ' - ', getSubstationName(routeEnd)) routeName, reportingTime, departureTime, ampm " +
						" from route r, loadingsheet l, route_time rt, time t, " +
						" (select f.* from route_detail f, route_detail t where f.substation = ? and t.substation = ? " +
						" and f.routeOrder "+ operand +" t.routeOrder and f.route = t.route) rd " +
						" where r.routeId = rd.route and r.routeId = rt.route and rt.routeTimeId = l.routeTime and rt.time = t.timeId " +
						" and rt.routeTimeId in (select routeTimeId from loadingsheet l, bus b, route_time rt, time t, route_time_seat rts, seat s where b.busId = l.bus and l.routeTime = rt.routeTimeId and t.timeId = rt.time and rt.routeTimeId = rts.routeTime and s.seatId = rts.seat and isAvailable = 1) " +
						" and date between ? and ? order by routeId, timeId ";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("fromSubstation"));
				statement.setString(2, sp.param.get("toSubstation"));
				statement.setString(3, sp.param.get("startDate"));
				statement.setString(4, sp.param.get("endDate"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult saveBookings(ServiceParameter sp, Current __current) {
		String methodName = "saveBookings";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				activeConnection.setAutoCommit(false);
	
	    		Map<String, String> result = new HashMap<String, String>();
				List<Boolean> booleanList = new ArrayList<Boolean>();
				
	    		for (String key : sp.param.keySet()) {
	    			Map<String, String> booking = new HashMap<String, String>();
	    			
	    			for(String element : sp.param.get(key).split(elementSeparator)) {
	    				String id = Arrays.asList(element.split(itemValueSeparator)).get(0);
	    				
	    				if(element.split(itemValueSeparator).length > 1) {
	    					String value = Arrays.asList(element.split(itemValueSeparator)).get(1);
		    				booking.put(id, value);
	    				} else {
	    					booking.put(id, null);
	    				}
	    			}
	    			
	    			Integer bookingId = saveBookingToDb(activeConnection, booking);

					if (bookingId != null) {
						
						Boolean seatStatusChanged = changeSeatStatus(activeConnection, Integer.parseInt(booking.get("seatId")), Integer.parseInt(booking.get("seatStatus")));
						Boolean bookingHistory = createBookingHistory(activeConnection, Integer.parseInt(booking.get("bookingAction")), bookingId, Integer.parseInt(booking.get("userId")), "booked and paid.");
						String actionDate = getActionDateBooking(activeConnection, bookingId, Integer.parseInt(booking.get("bookingAction")));
						
						String b = sp.param.get(key);
						b += elementSeparator +"bookingId"+ itemValueSeparator + bookingId;
						b += elementSeparator +"actionDate"+ itemValueSeparator + actionDate;
						result.put(key, b);
						
						booleanList.add(seatStatusChanged);
						booleanList.add(bookingHistory);
					} else {
						throw new SQLException("An error occured "+ methodName);
					}
	    		}
	    		
	    		if(booleanList.contains(false)) {
	    			throw new SQLException("An error occured "+ methodName);
	    		} else {
					sr.status = true;
				
					activeConnection.commit();
					activeConnection.setAutoCommit(true);
					sr.param = result;
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
	    		}
			} catch (TException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = e.errorDescription;
				logMessage(MESSAGETYPE.ERR, "TException at "+ methodName +": " + e.errorDescription);
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getTimes(ServiceParameter sp, Current __current) {
		String methodName = "getTimes";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = " select * from time ";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult addTime(ServiceParameter sp, Current __current) {
		String methodName = "addTime";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				
				String sqlQuery = "insert into time (reportingTime, departureTime, ampm) values (?, ?, ?)";
				
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("reportingTime"));
				statement.setString(2, sp.param.get("departureTime"));
				statement.setString(3, sp.param.get("ampm"));
				
				int result = statement.executeUpdate();
				
				if (result == 1) {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = true;
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = false;
					sr.message = "An error occured.";
					throw new SQLException("An error occured  "+ methodName );
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult editTime(ServiceParameter sp, Current __current) {
		String methodName = "editTime";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				
				String sqlQuery = "update time set reportingTime = ?, departureTime = ?, ampm = ? where TimeId = ?";
				
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("reportingTime"));
				statement.setString(2, sp.param.get("departureTime"));
				statement.setString(3, sp.param.get("ampm"));
				statement.setInt(4, Integer.parseInt(sp.param.get("timeId")));
				
				int result = statement.executeUpdate();
				
				if (result == 1) {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = true;
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = false;
					sr.message = "An error occured.";
					throw new SQLException("An error occured "+ methodName );
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	@Override
	public ServiceResult getRoutes(ServiceParameter sp, Current __current) {
		String methodName = "getRoutes";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = " select r.*, description routeStatusName, routeStatusId, getSubstationName(routeStart) routeStartName, getSubstationName(routeEnd) routeEndName from route r, route_status rs where r.routeStatus = rs.routeStatusId ";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult addRoute(ServiceParameter sp, Current __current) {
		String methodName = "addRoute";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				activeConnection.setAutoCommit(false);
	    		
	    		String sqlQuery = "insert into route (routeStart, routeEnd, routeStatus, dateCreated, amount, amountKg) values (?, ?, ?, now(), 0, 0)";
				
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("routeStart"));
				statement.setString(2, sp.param.get("routeEnd"));
				statement.setString(3, sp.param.get("routeStatus"));
				
				int result = statement.executeUpdate();
				
				if (result == 1) {
					ResultSet resultSet = statement.getGeneratedKeys();
					resultSet.next();
					Integer routeId = resultSet.getInt(1);
					
					Boolean startRouteDetail = createRouteDetail(activeConnection, routeId.toString(), sp.param.get("routeStart"), 1, 1, 0, 1);
					Boolean endRouteDetail = createRouteDetail(activeConnection, routeId.toString(), sp.param.get("routeEnd"), 1, 0, 1, 2);
					
					if(startRouteDetail && endRouteDetail) {
						activeConnection.commit();
						activeConnection.setAutoCommit(true);
						if(debug)
							System.out.println(methodName+" returned result, " + getMapValues(sr.param));
						
						closeConnectionStatementResultSet(activeConnection, statement, null);
						sr.status = true;
					} else {
						closeConnectionStatementResultSet(activeConnection, statement, null);
						throw new SQLException("An error occured  "+ methodName ); 
					}
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					throw new SQLException("An error occured  "+ methodName );
				}
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Create a route detail.
	 * 
	 * @param activeConnection
	 * @param route
	 * @param substation
	 * @param isStartEnd
	 * @param isStart
	 * @param isEnd
	 * @param routeOrder
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean createRouteDetail(Connection activeConnection, String route, String substation, Integer isStartEnd, Integer isStart, Integer isEnd, Integer routeOrder) throws Exception{
		Boolean resultBoolean = false;
		String sqlQuery = "insert into route_detail (route, substation, isStartEnd, isStart, isEnd, routeOrder) values(?, ?, ?, ?, ?, ?)";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, Integer.parseInt(route));
		statement.setInt(2, Integer.parseInt(substation)); 
		statement.setInt(3, isStartEnd);
		statement.setInt(4, isStart);
		statement.setInt(5, isEnd);
		statement.setInt(6, routeOrder);
		
		int result = statement.executeUpdate();
		
		if (result == 1) {
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured at createRouteDetail.");
		}
		return resultBoolean;
	}

	@Override
	public ServiceResult editRoute(ServiceParameter sp, Current __current) {
		String methodName = "editRoute";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				
				String sqlQuery = "update route set routeStatus = ? where routeId = ?";
				
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setInt(1, Integer.parseInt(sp.param.get("routeStatus")));
				statement.setInt(2, Integer.parseInt(sp.param.get("routeId")));
				
				int result = statement.executeUpdate();
				
				if (result == 1) {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = true;
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = false;
					sr.message = "An error occured.";
					throw new SQLException("An error occured "+ methodName );
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getRouteDetails(ServiceParameter sp, Current __current) {
		String methodName = "getRouteDetails";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = " select s.* from substation s, route_detail rd where s.substationId = rd.substation and route = ? order by routeOrder ";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setInt(1, Integer.parseInt(sp.param.get("routeId")));
				Boolean getAvailable = Boolean.parseBoolean(sp.param.get("getAvailable"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					
					if(getAvailable)
						sr.param.put("available", getAvailableSubstationsForRoute(activeConnection, Integer.parseInt(sp.param.get("routeId"))));
					
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Get th available substations for the selected route.
	 * 
	 * @param activeConnection
	 * @param routeId
	 * 
	 * @return XML data containing records of substation table.
	 * 
	 * @throws Exception
	 */
	private String getAvailableSubstationsForRoute(Connection activeConnection, Integer routeId) throws Exception {
		String result = null;
		
		String sqlQuery = " select * from substation where substationid not in(select substation from route_detail rd where route = ?) ";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, routeId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			resultSet.beforeFirst();
			result = XmlConverter.getXmlData(resultSet);
			
			closeConnectionStatementResultSet(activeConnection, statement, resultSet);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
		}
		
		return result;
	}

	@Override
	public ServiceResult saveRouteDetails(ServiceParameter sp, Current __current) {
		String methodName = "saveRouteDetails";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				activeConnection.setAutoCommit(false);
	
				String [] routeDetails = sp.param.get("routeDetails").split(elementSeparator);
				String route = sp.param.get("routeId");
				String routeStart = null;
				String routeEnd = null;
				List<Boolean> booleanList = new ArrayList<Boolean>();
				
				Boolean emptyRouteDetails = deleteRouteDetails(activeConnection, route);
				booleanList.add(emptyRouteDetails);
				
				for (int i = 0; i < routeDetails.length; i++) {
					Integer isStart = i == 0 ? 1 : 0;
					Integer isEnd = i == routeDetails.length - 1 ? 1 : 0;
					Integer isStartEnd = i == 0 | i == routeDetails.length - 1  ? 1 : 0;
					
					String element = routeDetails[i];
    				Integer routeOrder = Integer.parseInt(Arrays.asList(element.split(itemValueSeparator)).get(0));
    				
    				if(element.split(itemValueSeparator).length > 1) {
    					String substation = Arrays.asList(element.split(itemValueSeparator)).get(1);
    					
    					Boolean routeDetailCreated = createRouteDetail(activeConnection, route, substation, isStartEnd, isStart, isEnd, routeOrder);
	    				booleanList.add(routeDetailCreated);
	    				
	    				if(isStart ==1)
	    					routeStart = substation;
	    				if(isEnd ==1)
	    					routeEnd = substation;
    				} else {
    					throw new SQLException("An error occured "+ methodName);
    				}
    			}
				
				Boolean editRouteStartEnd = editRouteStartEnd(activeConnection, route, routeStart, routeEnd);
				booleanList.add(editRouteStartEnd);
	    		
	    		if(booleanList.contains(false)) {
	    			throw new SQLException("An error occured "+ methodName);
	    		} else {
					sr.status = true;
				
					activeConnection.commit();
					activeConnection.setAutoCommit(true);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
	    		}
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Delete all route details for a route.
	 * 
	 * @param activeConnection
	 * @param route
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean deleteRouteDetails(Connection activeConnection, String route) throws Exception {
		Boolean resultBoolean = false;
		String sqlQuery = " delete from route_detail where route = ? ";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, Integer.parseInt(route));
		
		int result = statement.executeUpdate();
		
		if (result >= 0) { // the number deleted records are return, so that can be 0 or more.
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured at deleteRouteDetails.");
		}
		return resultBoolean;
	}
	
	/**
	 * Delete all route details for a route.
	 * 
	 * @param activeConnection
	 * @param route
	 * @param routeStart
	 * @param routeEnd
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean editRouteStartEnd(Connection activeConnection, String route, String routeStart, String routeEnd) throws Exception {
		Boolean resultBoolean = false;
		String sqlQuery = "update route set routeStart = ?, routeEnd = ? where routeId = ?";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, Integer.parseInt(routeStart));
		statement.setInt(2, Integer.parseInt(routeEnd));
		statement.setInt(3, Integer.parseInt(route));
		
		int result = statement.executeUpdate();
		
		if (result == 1) { 
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured at editRouteStartEnd.");
		}
		return resultBoolean;
	}

	@Override
	public ServiceResult getRouteTimes(ServiceParameter sp, Current __current) {
		String methodName = "getRouteTimes";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = " select t.* from route_time rt, time t where rt.time = t.timeId and route = ? and isActive = 1 ";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setInt(1, Integer.parseInt(sp.param.get("routeId")));
				Boolean getAvailable = Boolean.parseBoolean(sp.param.get("getAvailable"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					sr.param.put("inActive", getInActiveTimesForRoute(activeConnection, Integer.parseInt(sp.param.get("routeId"))));
					
					if(getAvailable)
						sr.param.put("available", getAvailableTimesForRoute(activeConnection, Integer.parseInt(sp.param.get("routeId"))));
					
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					sr.param.put("inActive", getInActiveTimesForRoute(activeConnection, Integer.parseInt(sp.param.get("routeId"))));
					
					if(getAvailable)
						sr.param.put("available", getAvailableTimesForRoute(activeConnection, Integer.parseInt(sp.param.get("routeId"))));
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Get the inactive times for the selected route.
	 * 
	 * @param activeConnection
	 * @param routeId
	 * 
	 * @return XML data containing records of times table.
	 * 
	 * @throws Exception
	 */
	private String getInActiveTimesForRoute(Connection activeConnection, Integer routeId) throws Exception {
		String result = null;
		
		String sqlQuery = " select t.* from route_time rt, time t where rt.time = t.timeId and route = ? and isActive = 0 ";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, routeId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			resultSet.beforeFirst();
			result = XmlConverter.getXmlData(resultSet);
			
			closeConnectionStatementResultSet(activeConnection, statement, resultSet);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
		}
		
		return result;
	}
	
	/**
	 * Get the available times for the selected route.
	 * 
	 * @param activeConnection
	 * @param routeId
	 * 
	 * @return XML data containing records of times table.
	 * 
	 * @throws Exception
	 */
	private String getAvailableTimesForRoute(Connection activeConnection, Integer routeId) throws Exception {
		String result = null;
		
		String sqlQuery = " select * from time where timeId not in (select time from route_time rt where route = ? and (isActive = 1 or isActive = 0)) ";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, routeId);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			resultSet.beforeFirst();
			result = XmlConverter.getXmlData(resultSet);
			
			closeConnectionStatementResultSet(activeConnection, statement, resultSet);
		} else {
			closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
		}
		
		return result;
	}

	@Override
	public ServiceResult saveRouteTimes(ServiceParameter sp, Current __current) {
		String methodName = "saveRouteTimes";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				activeConnection.setAutoCommit(false);
	
				String route = sp.param.get("routeId");
				List<Boolean> booleanList = new ArrayList<Boolean>();
				
				String setActive = sp.param.get("setActive");
				if(setActive != null && !"".equalsIgnoreCase(setActive)) {
					for (String element : Arrays.asList(setActive.split(elementSeparator))) {
						if(element.split(itemValueSeparator).length > 1) {
	    					Integer value = Integer.parseInt(Arrays.asList(element.split(itemValueSeparator)).get(1));
	    					
	    					Boolean changeRouteTimeStatus = changeRouteTimeStatus(activeConnection, route, value, 1);
		    				booleanList.add(changeRouteTimeStatus);
	    				} else {
	    					throw new SQLException("An error occured "+ methodName);
	    				}
					}
				}
				
				String setInActive = sp.param.get("setInActive");
				if(setInActive != null && !"".equalsIgnoreCase(setInActive)) {
					for (String element : Arrays.asList(setInActive.split(elementSeparator))) {
						if(element.split(itemValueSeparator).length > 1) {
	    					Integer value = Integer.parseInt(Arrays.asList(element.split(itemValueSeparator)).get(1));
	    					
	    					Boolean changeRouteTimeStatus = changeRouteTimeStatus(activeConnection, route, value, 0);
		    				booleanList.add(changeRouteTimeStatus);
	    				} else {
	    					throw new SQLException("An error occured "+ methodName);
	    				}
					}
				}
				
				String createNew = sp.param.get("createNew");
				if(createNew != null && !"".equalsIgnoreCase(createNew)) {
					for (String element : Arrays.asList(createNew.split(elementSeparator))) {
						if(element.split(itemValueSeparator).length > 1) {
	    					Integer value = Integer.parseInt(Arrays.asList(element.split(itemValueSeparator)).get(1));
	    					
	    					Boolean createRouteTime = createRouteTime(activeConnection, route, value);
		    				booleanList.add(createRouteTime);
	    				} else {
	    					throw new SQLException("An error occured "+ methodName);
	    				}
					}
				}
				
	    		if(booleanList.contains(false)) {
	    			throw new SQLException("An error occured "+ methodName);
	    		} else {
					sr.status = true;
				
					activeConnection.commit();
					activeConnection.setAutoCommit(true);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
	    		}
			} catch (SQLException e) { 
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				activeConnection.rollback();
				activeConnection.setAutoCommit(true);
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
	
	/**
	 * Change the status of a route time record.
	 * 
	 * @param activeConnection
	 * @param route
	 * @param time
	 * @param status
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean changeRouteTimeStatus(Connection activeConnection, String route, Integer time, Integer status) throws Exception {
		Boolean resultBoolean = false;
		String sqlQuery = " update route_time set isActive = ? where route = ? and time = ? ";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, status);
		statement.setInt(2, Integer.parseInt(route));
		statement.setInt(3, time);
		
		int result = statement.executeUpdate();
		
		if (result == 1) { 
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured at deleteRouteDetails.");
		}
		return resultBoolean;
	}
	
	/**
	 * Create a route time.
	 * 
	 * @param activeConnection
	 * @param route
	 * @param time
	 * 
	 * @return Boolean
	 * 
	 * @throws Exception
	 */
	private Boolean createRouteTime(Connection activeConnection, String route, Integer time) throws Exception{
		Boolean resultBoolean = false;
		String sqlQuery = "insert into route_time (route, time, isActive) values(?, ?, 1)";
		
		CallableStatement statement = activeConnection.prepareCall(sqlQuery);
		statement.setInt(1, Integer.parseInt(route));
		statement.setInt(2, time); 
		
		int result = statement.executeUpdate();
		
		if (result == 1) {
			resultBoolean = true;
			
			closeConnectionStatementResultSet(activeConnection, statement, null);
		} else {
			resultBoolean = false;
			closeConnectionStatementResultSet(activeConnection, statement, null);
			throw new SQLException("An error occured at createRouteTime.");
		}
		return resultBoolean;
	}
	
	@Override
	public ServiceResult getSeats(ServiceParameter sp, Current __current) {
		String methodName = "getSeats";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = " select * from seat ";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult addSeat(ServiceParameter sp, Current __current) {
		String methodName = "addSeat";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				
				String sqlQuery = "insert into seat (description) values (?)";
				
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("description"));
				
				int result = statement.executeUpdate();
				
				if (result == 1) {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = true;
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = false;
					sr.message = "An error occured.";
					throw new SQLException("An error occured  "+ methodName );
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult editSeat(ServiceParameter sp, Current __current) {
		String methodName = "editSeat";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		Connection activeConnection = null;
		try {
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				activeConnection = getActiveDatabaseConnection();
				
				String sqlQuery = "update seat set description = ? where seatId = ?";
				
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("description"));
				statement.setInt(2, Integer.parseInt(sp.param.get("seatId")));
				
				int result = statement.executeUpdate();
				
				if (result == 1) {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = true;
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, null);
					sr.status = false;
					sr.message = "An error occured.";
					throw new SQLException("An error occured "+ methodName );
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}

	@Override
	public ServiceResult getLoadingsheetByDates(ServiceParameter sp,
			Current __current) {
		String methodName = "getLoadingsheetByDate";
		ServiceResult sr = new ServiceResult(false, "", new HashMap<String, String>());
		try {
			String result = null;
			if(debug)
				System.out.println(methodName+" requested, " + getMapValues(sp.param));
			
			try {
				String sqlQuery = " select *, getSubstationName(origin) originName, getSubstationName(destination) destinationName " +
								" , (select registrationNumber from bus where busId = l.bus) busRegistrationNumber " +
								" , (select concat(firstname, ' ', lastname) from employee where employeeId = l.driver) driverName " +
								" , (select concat(firstname, ' ', lastname) from employee where employeeId = l.conductor) conductorName " +
								" , (select description from loadingsheet_status where loadingsheetStatusId = l.loadingsheetStatus) loadingsheetStatusName " +
								" , (select concat(departureTime, ' ', ampm) from route_time, time where time = timeId and routeTimeId = l.routeTime) routeTimeName " +
								" from loadingsheet l where date between ? and ?";
				
				Connection activeConnection = getActiveDatabaseConnection();
				CallableStatement statement = activeConnection.prepareCall(sqlQuery);
				
				statement.setString(1, sp.param.get("startDate"));
				statement.setString(2, sp.param.get("endDate"));
				
				ResultSet resultSet = statement.executeQuery();
				
				if (resultSet.next()) {
					resultSet.beforeFirst();
					result = XmlConverter.getXmlData(resultSet);
					
					sr.param.put("RECORDS", result);
					
					sr.status = true;
					
					closeConnectionStatementResultSet(activeConnection, statement, resultSet);
					if(debug)
						System.out.println(methodName+" returned result, " + getMapValues(sr.param));
				} else {
					closeConnectionStatementResultSet(activeConnection, statement, resultSet); // no data found.
					sr.status = true;
				}
			} catch (SQLException e) { 
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "SQLException at "+ methodName +": " + e);
			} catch (Exception e) {
				sr.status = false;
				sr.message = "An error occured.";
				logMessage(MESSAGETYPE.ERR, "Unknown error 1 at "+ methodName +": " + e);
			}
		} catch (Exception e) {
			sr.status = false;
			sr.message = "An error occured.";
			logMessage(MESSAGETYPE.ERR, "Unknown error 2 at "+ methodName +": " + e);
		}
		return sr;
	}
}
